﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WS.EKA.Business.Model;
using Oracle.DataAccess.Client;
using WS.EKA.Business.Utilty;
using System.Data;
namespace WS.EKA.Business.Dal
{
    public class EventReportAccess
    {
        static IDB DBHelper = DBFactory.CreateBODB();

        #region Daily
        /// <summary>
        /// 日零担开单量 -今日&上周同期
        /// </summary>
        /// <returns></returns>
        public static EventCount GetDailyLTTLCount()
        {
            return GetDailyCountByType("tb_Daily_LTTL");
        }

        /// <summary>
        /// 日快递开单量 -今日&上周同期
        /// </summary>
        /// <returns></returns>
        public static EventCount GetDailyEDCount()
        {
            return GetDailyCountByType("tb_Daily_ED");
        }

        private static EventCount GetDailyCountByType(string type)
        {
            string sql = @"select cast(sum(bs_count) as number(9,0)) as count from " + type +
                         @" where trunc(createtime,'dd')=trunc(sysdate,'dd')
                            union
                           select cast(sum(bs_count) as number(9,0)) as count  from " + type +
                         @" where trunc(createtime,'dd')= trunc( sysdate-7,'dd')";
            var table = DBHelper.ExecuteDataTable(sql);

            int item1 = 0;
            int.TryParse(table.Rows[0][0].ToString(), out item1);
            int item2 = 0;
            int.TryParse(table.Rows[1][0].ToString(), out item2);
            var ret = new EventCount()
            {
                Item1 = item1,
                Item2 = item2,
            };
            return ret;
        }

        public static Dictionary<string, int> GetDailyLTTLTrendStatistics(int intervalMinutes, int hoursRegion)
        {
            return GetDailyTrendStatisticsByType("tb_Daily_LTTL", intervalMinutes, hoursRegion, 0);
        }

        public static Dictionary<string, int> GetDailyLTTLTrendStatisticsBefore(int intervalMinutes, int hoursRegion)
        {
            return GetDailyTrendStatisticsByType("tb_Daily_LTTL", intervalMinutes, hoursRegion, 7, true);
        }

        public static Dictionary<string, int> GetDailyEDTrendStatistics(int intervalMinutes, int hoursRegion)
        {
            return GetDailyTrendStatisticsByType("tb_Daily_ED", intervalMinutes, hoursRegion, 0);
        }

        public static Dictionary<string, int> GetEDTrendStatisticsBefore(int intervalMinutes, int hoursRegion)
        {
            return GetDailyTrendStatisticsByType("tb_Daily_ED", intervalMinutes, hoursRegion, 7, true);
        }

        private static Dictionary<string, int> GetDailyTrendStatisticsByType(string type, int intervalMinutes, int hoursRegion, int dayInterval, bool isLastWeek = false)
        {
            string sql = @"select cast(bs_count as number(9,0)) as bs_count,createtime from " + type + @"
                    where trunc(createtime,'mi')>= trunc(sysdate-" + dayInterval + @"-" + (hoursRegion - 1) + @"/24,'mi') 
                          and trunc(createtime,'mi')<= trunc(sysdate-" + dayInterval + @",'mi') 
                    order by createtime asc";

            var dataList = DBHelper.ExecuteDataTable(sql).ToList<EventReportBase>().ToArray();
            if (dataList.Length == 0)
                return new Dictionary<string, int>();

            int regions = hoursRegion * 60 / intervalMinutes;
            var result = new Dictionary<string, int>(regions);
            var time = dataList[dataList.Length - 1].CreateTime.AddHours(-hoursRegion);
            if (isLastWeek)
                time = time.AddDays(-7);

            int mod = time.Minute % intervalMinutes;
            if (mod != 0)
                time = time.AddMinutes(-mod);

            var maxTime = dataList[dataList.Length - 1].CreateTime;
            while (time <= maxTime)
            {
                int lowIndex = BinarySearch(time, dataList);
                time = time.AddMinutes(intervalMinutes);
                int upperIndex = BinarySearch(time, dataList);
                --upperIndex;
                if (upperIndex <=0)
                    continue;
                var arr = new EventReportBase[upperIndex - lowIndex + 1];
                Array.Copy(dataList, lowIndex, arr, 0, upperIndex - lowIndex + 1);
                int sum = arr.Sum(x => x.BS_Count);
                result.Add(time.ToString("HH:mm"), sum);
            }
            return result;
        }

        /// <summary>
        /// 找到小于当前时间段的最后一个数据索引
        /// </summary>
        /// <param name="key"></param>
        /// <param name="nums"></param>
        /// <returns></returns>
        private static int BinarySearch(DateTime key, EventReportBase[] nums)
        {
            int low = 0;
            int high = nums.Length - 1;
            int middle;
            while (low <= high)
            {
                middle = (low + high) / 2;
                if (key > nums[middle].CreateTime)
                {
                    low = middle + 1;
                }
                else if (key < nums[middle].CreateTime)
                {
                    high = middle - 1;
                }
                else
                {
                    return middle;
                }
            }
            return low;
        }
        #endregion

        #region Weekly

        public static EventCount GetWeeklyEDCount()
        {
            return GetWeekCountByType("tb_Weekly_ED");
        }

        public static EventCount GetWeeklyTLLTCount()
        {
            return GetWeekCountByType("tb_Weekly_LTTL");
        }

        public static Dictionary<string, int> GetWeeklyTLLTStatisticsOfCrrent()
        {
            return GetWeeklyStatisticsOfCurrentByType("tb_Weekly_LTTL");
        }

        public static Dictionary<string, int> GetWeeklyEDStatisticsOfCrrent()
        {
            return GetWeeklyStatisticsOfCurrentByType("tb_Weekly_ED");
        }

        public static Dictionary<string, int> GetWeeklyTLLTStatisticsOfBefore()
        {
            return GetWeeklyStatisticsOfBeforeByType("tb_Weekly_LTTL");
        }

        public static Dictionary<string, int> GetWeeklyEDStatisticsOfBefore()
        {
            return GetWeeklyStatisticsOfBeforeByType("tb_Weekly_ED");
        }

        private static EventCount GetWeekCountByType(string type)
        {
            string sql = @" select cast(sum(bs_count) as number(9,0))  as Count  from " + type +
                            @" where trunc(createtime,'dd')>=trunc(sysdate-to_char(sysdate -1,'d'),'dd') and createtime<sysdate
                            union
                            select sum(bs_count)  as Count from " + type +
                            @" where trunc(createtime,'dd')>=trunc(sysdate-to_char(sysdate -1,'d')-7,'dd')
                            and trunc(createtime,'dd')<trunc(sysdate-to_char(sysdate -1,'d'),'dd')";
            var table = DBHelper.ExecuteDataTable(sql);

            int item1 = 0;
            int.TryParse(table.Rows[0][0].ToString(), out item1);
            int item2 = 0;
            int.TryParse(table.Rows[1][0].ToString(), out item2);
            var ret = new EventCount()
            {
                Item1 = item1,
                Item2 = item2,
            };
            return ret;
        }

        private static Dictionary<string, int> GetWeeklyStatisticsOfCurrentByType(string type)
        {
            string sql = @"select cast(sum(bs_count) as number(9,0)) as count ,Create_Day from
                            (
                                select bs_count, trunc(createtime,'dd') as Create_Day  from " + type + @"
                                where trunc(createtime,'dd')>=trunc(sysdate-to_char(sysdate -1,'d'),'dd') and createtime<sysdate
                            ) T group by Create_Day order by Create_Day asc";
            var dataList = DBHelper.ExecuteDataTable(sql);
            var dic = new Dictionary<string, int>(7);
            foreach (DataRow row in dataList.Rows)
            {
                int value = 0;
                int.TryParse(row[0].ToString(), out value);
                dic.Add(row[1].ToString(), value);
            }
            return dic;
        }

        private static Dictionary<string, int> GetWeeklyStatisticsOfBeforeByType(string type)
        {
            string sql = @"select cast(sum(bs_count) as number(9,0)) as count ,Create_Day from
                            (
                            select bs_count, to_char(createtime,'yyyy-mm-dd') as Create_Day  from " + type + @"
                                  where trunc(createtime,'dd')>=trunc(sysdate-to_char(sysdate -1,'d')-7,'dd')
                                  and trunc(createtime,'dd')<trunc(sysdate-to_char(sysdate -1,'d'),'dd')
                            ) T group by Create_Day order by Create_Day asc";
            var dataList = DBHelper.ExecuteDataTable(sql);
            var dic = new Dictionary<string, int>(7);
            foreach (DataRow row in dataList.Rows)
            {
                int value = 0;
                int.TryParse(row[0].ToString(), out value);
                dic.Add(row[1].ToString(), value);
            }
            return dic;
        }

        #endregion
    }
}
